**************************************************
**************************************************
* TABLE OF OFFICER DEMOGRAPHICS (MATCHED)
**************************************************
**************************************************

* 1. Load masterfile
use "masterfile-in-geo-beats-for-analyses.dta", clear

* 2. Compute total stops and arrests by race
gen stp_tot = stp_blk + stp_wht + stp_hsp + stp_asn
gen arr_tot = arr_blk + arr_wht + arr_hsp + arr_asn

* 3. Simplify rank categories
gen rank2 = substr(off_rank, 1, 2)
replace off_rank = "officer"    if rank2 == "PO"
replace off_rank = "sergeant"   if inlist(rank2, "SE", "SG")
replace off_rank = "lieutenant" if rank2 == "LI"
drop rank2

* 4. Compute age in 2015
gen off_age2015 = 2015 - off_yob

* 5. Keep relevant variables
keep uid add_has_address assgn_year off_yob off_apt_date off_rank off_age2015 ///
     off_months_service off_gender off_race off_lives_district off_lives_beat ///
     uof_tot stp_tot arr_tot uof_blk uof_wht uof_hsp uof_asn stp_blk stp_wht stp_hsp stp_asn ///
     arr_blk arr_wht arr_hsp arr_asn

**************************************************
* ANNUAL MEANS BY OFFICER & RACE
**************************************************

foreach v in uof stp arr {
    bys uid assgn_year: egen `v'_tot_year = mean(`v'_tot)
    bys uid assgn_year: egen `v'_blk_year = mean(`v'_blk)
    bys uid assgn_year: egen `v'_wht_year = mean(`v'_wht)
    bys uid assgn_year: egen `v'_hsp_year = mean(`v'_hsp)
    bys uid assgn_year: egen `v'_asn_year = mean(`v'_asn)
}

* 6. Remove duplicates and collapse to officer level
duplicates drop
drop assgn_year
collapse (mean) *_year, by(uid add_has_address off_yob off_apt_date off_gender ///
                           off_race off_lives_district off_lives_beat off_rank off_age2015)

**************************************************
* CREATE DUMMY VARIABLES & LABELS FOR LATEX EXPORT
**************************************************

* Race indicators
gen black    = (off_race == "black")
lab var black    "\hspace{2mm} Black"

gen hispanic = (off_race == "hisp")
lab var hispanic "\hspace{2mm} Hispanic"

gen white    = (off_race == "white")
lab var white    "\hspace{2mm} White"

* Gender indicators
gen male     = (off_gender == "male")
lab var male     "\hspace{2mm} Male (\%)"

gen female   = (off_gender == "female")
lab var female   "\hspace{2mm} Female (\%)"

* Age label
lab var off_age2015 "\hspace{2mm} Age in 2015"

* Rank indicators
gen officer    = (off_rank == "officer")
lab var officer    "\hspace{2mm} Police officer (\%)"

gen sergeant   = (off_rank == "sergeant")
lab var sergeant   "\hspace{2mm} Sergeant (\%)"

gen lieutenant = (off_rank == "lieutenant")
lab var lieutenant "\hspace{2mm} Lieutenant (\%)"

* Residence indicators
lab var off_lives_district "\hspace{2mm} Lives in the district (\%)"
lab var off_lives_beat     "\hspace{2mm} Lives in the beat (\%)"

* Scale dummies to 0-100 to output percentages instead of shares
foreach var in male female officer sergeant lieutenant off_lives_district off_lives_beat {
    replace `var' = `var' * 100
}

* Annual mean labels for outcomes
foreach var in uof arr stp {
    lab var `var'_tot_year "\hspace{2mm} All civilians"
    lab var `var'_blk_year "\hspace{2mm} Black civilians"
    lab var `var'_hsp_year "\hspace{2mm} Hispanic civilians"
    lab var `var'_wht_year "\hspace{2mm} White civilians"
}

**************************************************
* DESCRIPTIVE TABLE BY RACE
**************************************************

local descriptives male female off_age2015 officer sergeant lieutenant off_lives_district off_lives_beat ///
                   uof_tot_year uof_blk_year uof_wht_year uof_hsp_year ///
                   stp_tot_year stp_blk_year stp_wht_year stp_hsp_year ///
                   arr_tot_year arr_blk_year arr_wht_year arr_hsp_year

foreach g in black hispanic white {
    estpost sum `descriptives' if `g' == 1
    estimates store des_`g'
    unique uid if `g' == 1
    local count_`g' = r(unique)
}

esttab ///
    des_black des_hispanic des_white ///
    using "desc-stats-by-race.tex", replace ///
    label ///
    noobs ///
    nonum ///
    collabels(none) ///
    mtitles("Black (N=`count_black')" "Hispanic (N=`count_hispanic')" "White (N=`count_white')") ///
    cell(mean(fmt(2)) sd(par fmt(2))) ///
    refcat( ///
        male              "\textit{Demographics}"      ///
        officer           "\textit{Rank}"              ///
        off_lives_district "\textit{Place of residence and work}" ///
        uof_tot_year      "\textit{Probability of using force during a shift (in\%)}" ///
        stp_tot_year      "\textit{Probability of making a stop during a shift (in\%)}"  ///
        arr_tot_year      "\textit{Probability of making an arrest during a shift (in\%)}", ///
        nolabel)
		
		
		

		
		
		
		
**************************************************		
**************************************************
* TABLE OF OFFICER DEMOGRAPHICS (UNMATCHED)
**************************************************
**************************************************

* 1. Load masterfile and restrict to active, unmatched officers
use "masterfile.dta", clear
keep if assgn_present == "true" & add_has_address == 0

* 2. Restrict to geographic beats, shifts 1–4, and districts 1–25
keep if inrange(assgn_shift, 1, 4)
keep if assgn_unit <= 25

* 3. Normalize beat codes and flag geographic beats
rename assgn_beat beat
while substr(beat, 1, 1) == "0" {
    replace beat = substr(beat, 2, .)
}
gen beat4 = regexs(1) if regexm(beat, "([0-9]{4})[-]*[0-9]*[ a-zA-Z]*$")
gen beat3 = regexs(1) if regexm(beat, "([0-9]{3})[-]*[0-9]*[ a-zA-Z]*$")
replace beat4 = beat3 if missing(beat4) & !missing(beat3)
rename beat4 beat_num
destring beat_num, replace

merge m:1 beat_num using "cpd-beats.dta", keepusing(beat_num)
gen assgn_beat_geographic = (_merge == 3)
drop _merge beat3

rename beat assgn_beat
rename beat_num assgn_beat_numerical_part

* 4. Drop malformed beat codes
gen to_drop = missing(assgn_beat)                                 ///
           | inlist(assgn_beat, "000", "0000", "00000", "000000")  ///
           | length(assgn_beat) <= 2                              ///
           | length(assgn_beat) > 6
gen firstchar = substr(assgn_beat, 1, 1)
destring firstchar, force replace
replace to_drop = 1 if missing(firstchar)
drop firstchar
drop if to_drop
drop to_drop

keep if assgn_beat_geographic == 1

**************************************************
* RENAME OUTCOME VARIABLES
**************************************************

foreach o in stp arr uof {
    rename `o'_blk_maf_all `o'_blk
    rename `o'_hsp_maf_all `o'_hsp
    rename `o'_wht_maf_all `o'_wht
    rename `o'_asn_maf_all `o'_asn
}

* 5. Compute total stops and arrests
gen stp_tot = stp_blk + stp_wht + stp_hsp + stp_asn
gen arr_tot = arr_blk + arr_wht + arr_hsp + arr_asn

**************************************************
* SIMPLIFY RANK AND AGE
**************************************************

gen rank2 = substr(off_rank, 1, 2)
replace off_rank = "officer"    if rank2 == "PO"
replace off_rank = "sergeant"   if inlist(rank2, "SE", "SG")
replace off_rank = "lieutenant" if rank2 == "LI"
drop rank2

gen off_age2015 = 2015 - off_yob

**************************************************
* SELECT VARIABLES FOR SUMMARY
**************************************************

keep uid assgn_year off_yob off_apt_date off_rank off_age2015 ///
     off_gender off_race uof_tot uof_blk uof_wht uof_hsp uof_asn ///
     stp_tot stp_blk stp_wht stp_hsp stp_asn arr_tot arr_blk arr_wht arr_hsp arr_asn

**************************************************
* COMPUTE ANNUAL MEANS BY OFFICER & RACE
**************************************************

foreach v in uof arr stp {
    bys uid assgn_year: egen `v'_tot_year = mean(`v'_tot)
    bys uid assgn_year: egen `v'_blk_year = mean(`v'_blk)
    bys uid assgn_year: egen `v'_wht_year = mean(`v'_wht)
    bys uid assgn_year: egen `v'_hsp_year = mean(`v'_hsp)
    bys uid assgn_year: egen `v'_asn_year = mean(`v'_asn)
}

duplicates drop
drop assgn_year

collapse (mean) *_year, by(uid off_yob off_apt_date off_gender off_race off_rank off_age2015)

**************************************************
* CREATE DUMMY VARIABLES AND LABELS
**************************************************

* Race
gen black    = (off_race == "black")
lab var black    "\hspace{2mm} Black"

gen hispanic = (off_race == "hisp")
lab var hispanic "\hspace{2mm} Hispanic"

gen white    = (off_race == "white")
lab var white    "\hspace{2mm} White"

* Gender
gen male  = (off_gender == "male")
lab var male  "\hspace{2mm} Male (\%)"

gen female = (off_gender == "female")
lab var female "\hspace{2mm} Female (\%)"

* Age
lab var off_age2015 "\hspace{2mm} Age in 2015"

* Rank
gen officer    = (off_rank == "officer")
lab var officer    "\hspace{2mm} Police officer (\%)"

gen sergeant   = (off_rank == "sergeant")
lab var sergeant   "\hspace{2mm} Sergeant (\%)"

gen lieutenant = (off_rank == "lieutenant")
lab var lieutenant "\hspace{2mm} Lieutenant (\%)"

**************************************************
* SCALE DUMMIES TO 0-100
**************************************************

foreach var in male female officer sergeant lieutenant {
    replace `var' = `var' * 100
}

foreach var in uof_tot_year uof_blk_year uof_wht_year uof_hsp_year ///
                arr_tot_year arr_blk_year arr_wht_year arr_hsp_year ///
                stp_tot_year stp_blk_year stp_wht_year stp_hsp_year {
    replace `var' = `var' * 100
}

**************************************************
* LABEL ANNUAL MEANS
**************************************************

foreach v in uof arr stp {
    lab var `v'_tot_year "\hspace{2mm} All civilians"
    lab var `v'_blk_year "\hspace{2mm} Black civilians"
    lab var `v'_hsp_year "\hspace{2mm} Hispanic civilians"
    lab var `v'_wht_year "\hspace{2mm} White civilians"
}

**************************************************
* DESCRIPTIVE TABLE BY RACE
**************************************************

local descriptives male female off_age2015 officer sergeant lieutenant ///
                   uof_tot_year uof_blk_year uof_wht_year uof_hsp_year ///
                   stp_tot_year stp_blk_year stp_wht_year stp_hsp_year ///
                   arr_tot_year arr_blk_year arr_wht_year arr_hsp_year

foreach g in black hispanic white {
    estpost sum `descriptives' if `g' == 1
    estimates store des_`g'
}

esttab des_black des_hispanic des_white ///
    using "desc-stats-by-race-unmatched.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black (N=365)" "Hispanic (N=711)" "White (N=1193)") ///
    cell(mean(fmt(2)) sd(par fmt(2))) ///
    refcat( ///
      male   "\textit{Demographics}" ///
      officer "\textit{Rank}" ///
      uof_tot_year "\textit{Probability of using force during a shift (in\%)}" ///
      stp_tot_year "\textit{Probability of making a stop during a shift (in\%)}" ///
      arr_tot_year "\textit{Probability of making an arrest during a shift (in\%)}", nolabel)
	  
	  
	  
	  
	  
************************************************** 
**************************************************
* PROBABILITY OF FORCE WITH AND WITHOUT INJURY
**************************************************
**************************************************

* 1. Load masterfile
use "masterfile-in-geo-beats-for-analyses.dta", clear

**************************************************
* RENAME UOF INJURY VARIABLES
**************************************************

* Rename injury-specific use‑of‑force indicators
ren uof_*_maf_yin uofyin_*
ren uof_*_maf_nin uofnin_*

**************************************************
* COMPUTE TOTAL PROBABILITIES
**************************************************

gen uofyin_tot = uofyin_blk + uofyin_wht + uofyin_hsp + uofyin_asn
gen uofnin_tot = uofnin_blk + uofnin_wht + uofnin_hsp + uofnin_asn

**************************************************
* VARIABLE LABELS FOR UOF INJURY RATES
**************************************************

foreach var in uofyin uofnin {
    lab var `var'_tot  "\\[-1ex]  \hspace{2mm} All civilians"
    lab var `var'_blk  "\\[-1ex]  \hspace{2mm} Black civilians"
    lab var `var'_hsp  "\\[-1ex]  \hspace{2mm} Hispanic civilians"
    lab var `var'_wht  "\\[-1ex]  \hspace{2mm} White civilians"
}

**************************************************
* DESCRIPTIVE STATISTICS BY OFFICER RACE
**************************************************

foreach g in black hisp white {
    estpost sum ///
        uofnin_tot uofnin_blk uofnin_wht uofnin_hsp ///
        uofyin_tot uofyin_blk uofyin_wht uofyin_hsp ///
        if off_race == "`g'"
    estimates store des_`g'
}

**************************************************
* EXPORT TABLE TO LaTeX
**************************************************

esttab ///
    des_black des_hisp des_white ///
    using "desc-stats-uof-injury-by-race.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black Officers" "Hispanic Officers" "White Officers") ///
    refcat( ///
        uofnin_tot "\\[-1ex] \\textit{Probability of UOF without injury to the civilian (in\\%)}" ///
        uofyin_tot "\\[-1ex] \\textit{Probability of UOF with injury to the civilian (in\\%)}", ///
        nolabel) ///
    cell(mean(fmt(3)))	  
	
	
	
	
	
**************************************************
**************************************************
* TABLE OF OFFICER EXPOSURE TO HOMICIDES PRIOR TO ASSIGNMENT
**************************************************
**************************************************

* 1. Load data
use "masterfile-in-geo-beats-for-analyses.dta", clear

**************************************************
* 2. Combine adjacent distance quartiles (q1+q2 and q1–q4)
**************************************************

gen cri_hom_q1q2_1wb = (cri_hom_q1_1wb == 1 | cri_hom_q2_1wb == 1)
gen cri_hom_q1q4_1wb = (cri_hom_q1_1wb == 1 | cri_hom_q2_1wb == 1 | cri_hom_q3_1wb == 1 | cri_hom_q4_1wb == 1)

foreach v in v_blk_p_blk v_blk_p_hsp v_blk_p_wht v_blk_p_unk ///
            v_hsp_p_blk v_hsp_p_hsp v_hsp_p_wht v_hsp_p_unk ///
            v_wht_p_blk v_wht_p_hsp v_wht_p_wht v_wht_p_unk {
    gen cri_hom_q1q2_`v'_1wb = (cri_hom_q1_`v'_1wb == 1 | cri_hom_q2_`v'_1wb == 1)
    gen cri_hom_q1q4_`v'_1wb = (cri_hom_q1_`v'_1wb == 1 | cri_hom_q2_`v'_1wb == 1 | cri_hom_q3_`v'_1wb == 1 | cri_hom_q4_`v'_1wb == 1)
}

**************************************************
* 3. Rename and initialize exposure counts
**************************************************

rename cri_hom_q1q2* homquart*
foreach v of varlist homquart_* {
    foreach r in black hisp white {
        sum `v' if off_race == "`r'"
        gen `v'_`r' = (r(mean) * r(N))
    }
}

**************************************************
* 4. Assign counts by officer race
**************************************************

foreach v in homquart_v_blk_p_blk_1wb homquart_v_blk_p_hsp_1wb homquart_v_blk_p_wht_1wb homquart_v_blk_p_unk_1wb ///
            homquart_v_hsp_p_blk_1wb homquart_v_hsp_p_hsp_1wb homquart_v_hsp_p_wht_1wb homquart_v_hsp_p_unk_1wb ///
            homquart_v_wht_p_blk_1wb homquart_v_wht_p_hsp_1wb homquart_v_wht_p_wht_1wb homquart_v_wht_p_unk_1wb {
    replace `v' = .
}
foreach v in homquart_v_blk_p_blk_1wb homquart_v_blk_p_hsp_1wb homquart_v_blk_p_wht_1wb homquart_v_blk_p_unk_1wb ///
            homquart_v_hsp_p_blk_1wb homquart_v_hsp_p_hsp_1wb homquart_v_hsp_p_wht_1wb homquart_v_hsp_p_unk_1wb ///
            homquart_v_wht_p_blk_1wb homquart_v_wht_p_hsp_1wb homquart_v_wht_p_wht_1wb homquart_v_wht_p_unk_1wb {
    foreach r in black hisp white {
        replace `v' = `v'_`r' if off_race == "`r'"
    }
}

**************************************************
* 5. Label variables by suspect race
**************************************************

foreach var of varlist *_p_blk_1wb {
    lab var `var' "\hspace{2mm} Black susp."
}
foreach var of varlist *_p_wht_1wb {
    lab var `var' "\hspace{2mm} White susp."
}
foreach var of varlist *_p_hsp_1wb {
    lab var `var' "\hspace{2mm} Hispanic susp."
}
foreach var of varlist *_p_unk_1wb {
    lab var `var' "\hspace{2mm} No arrest made"
}

**************************************************
* 6. Descriptive statistics by officer race
**************************************************

foreach r in black hisp white {
    estpost sum ///
        homquart_v_blk_p_blk_1wb homquart_v_blk_p_hsp_1wb homquart_v_blk_p_wht_1wb homquart_v_blk_p_unk_1wb ///
        homquart_v_hsp_p_blk_1wb homquart_v_hsp_p_hsp_1wb homquart_v_hsp_p_wht_1wb homquart_v_hsp_p_unk_1wb ///
        homquart_v_wht_p_blk_1wb homquart_v_wht_p_hsp_1wb homquart_v_wht_p_wht_1wb homquart_v_wht_p_unk_1wb ///
        if off_race == "`r'"
    estimates store des_`r'
}

**************************************************
* 7. Export to LaTeX
**************************************************

esttab des_black des_hisp des_white ///
    using "desc-stats-crime-exposure-by-race.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black officer‑shifts" "Hispanic officer‑shifts" "White officer‑shifts") ///
    refcat( ///
        homquart_v_blk_p_blk_1wb "\textit{Black victim}" ///
        homquart_v_hsp_p_blk_1wb "\textit{Hispanic victim}" ///
        homquart_v_wht_p_blk_1wb "\textit{White victim}" , nolabel) ///
    cell(mean(fmt(0)))	
	
	

	
	
**************************************************
* TABLE OF OFFICER EXPOSURE TO HOMICIDES
*    WITHIN DIFFERENT BINS
**************************************************

* 1. Load data
use "masterfile-in-geo-beats-for-analyses.dta", clear

**************************************************
* 2. Rename homicide exposure variables
**************************************************

* 2.1 Distance 1/8 mile
ren cri_hom_q1* homeight*
* 2.2 Distance 1/4 mile
ren cri_hom_q2* homquart*
* 2.3 Distance 3/8 mile
ren cri_hom_q3* homthree8*

**************************************************
* 3. Compute combined exposure indicators
**************************************************

foreach v of varlist v_blk_p_blk v_blk_p_hsp v_blk_p_wht v_blk_p_unk ///
                   v_hsp_p_blk v_hsp_p_hsp v_hsp_p_wht v_hsp_p_unk ///
                   v_wht_p_blk v_wht_p_hsp v_wht_p_wht v_wht_p_unk {
    * 3.1 Combine 1/8 to 1/4‑mile exposures
    gen cri_hom_q1q2_`v'_1wb = (cri_hom_q1_`v'_1wb == 1 | cri_hom_q2_`v'_1wb == 1)
    * 3.2 Combine 1/8 to 1/2‑mile exposures
    gen cri_hom_q1q4_`v'_1wb = ///
        (cri_hom_q1_`v'_1wb == 1 | cri_hom_q2_`v'_1wb == 1 | ///
         cri_hom_q3_`v'_1wb == 1 | cri_hom_q4_`v'_1wb == 1)
}

**************************************************
* 4. Initialize race‑specific counts
**************************************************

* 4.1 Rename for uniform prefix
rename cri_hom_q1q2* homquart*

* 4.2 Compute counts for each officer race
foreach v of varlist homquart_* {
    foreach r in black hisp white {
        sum `v' if off_race == "`r'"
        gen `v'_`r' = (r(mean) * r(N))
    }
}

* 4.3 Reset originals to missing
foreach v of varlist homquart_* {
    replace `v' = .
}

* 4.4 Assign race‑specific counts back
foreach v of varlist homquart_* {
    foreach r in black hisp white {
        replace `v' = `v'_`r' if off_race == "`r'"
    }
}

**************************************************
* 5. Label suspect‑race variables
**************************************************

foreach var of varlist *_p_blk_1wb {
    label var `var' "\hspace{2mm} Black susp."
}
foreach var of varlist *_p_wht_1wb {
    label var `var' "\hspace{2mm} White susp."
}
foreach var of varlist *_p_hsp_1wb {
    label var `var' "\hspace{2mm} Hispanic susp."
}
foreach var of varlist *_p_unk_1wb {
    label var `var' "\hspace{2mm} No arrest made"
}

**************************************************
* 6. Descriptive statistics by officer race
**************************************************

* 6.1 Exposure within 1/8 mile
foreach r in black hisp white {
    estpost sum homeight_v_blk_p_blk_1wb homeight_v_blk_p_hsp_1wb  ///
        homeight_v_blk_p_wht_1wb homeight_v_blk_p_unk_1wb       ///
        homeight_v_hsp_p_blk_1wb homeight_v_hsp_p_hsp_1wb       ///
        homeight_v_hsp_p_wht_1wb homeight_v_hsp_p_unk_1wb       ///
        homeight_v_wht_p_blk_1wb homeight_v_wht_p_hsp_1wb       ///
        homeight_v_wht_p_wht_1wb homeight_v_wht_p_unk_1wb       ///
        if off_race == "`r'"
    estimates store des_`r'
}

esttab des_black des_hisp des_white ///
    using "desc-stats-crime-exposure-by-race-1eight.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black officer‑shifts" "Hispanic officer‑shifts" "White officer‑shifts") ///
    refcat( ///
        homeight_v_blk_p_blk_1wb "\textit{Black victim}" ///
        homeight_v_hsp_p_blk_1wb "\textit{Hispanic victim}" ///
        homeight_v_wht_p_blk_1wb "\textit{White victim}", ///
        nolabel) ///
    cell(mean(fmt(0)))

* 6.2 Exposure within 1/4 mile (quartile)
foreach r in black hisp white {
    estpost sum homquart_v_blk_p_blk_1wb homquart_v_blk_p_hsp_1wb ///
        homquart_v_blk_p_wht_1wb homquart_v_blk_p_unk_1wb          ///
        homquart_v_hsp_p_blk_1wb homquart_v_hsp_p_hsp_1wb          ///
        homquart_v_hsp_p_wht_1wb homquart_v_hsp_p_unk_1wb          ///
        homquart_v_wht_p_blk_1wb homquart_v_wht_p_hsp_1wb          ///
        homquart_v_wht_p_wht_1wb homquart_v_wht_p_unk_1wb          ///
        if off_race == "`r'"
    estimates store des_`r'
}

esttab des_black des_hisp des_white ///
    using "desc-stats-crime-exposure-by-race-1week-1quarter.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black officer‑shifts" "Hispanic officer‑shifts" "White officer‑shifts") ///
    refcat( ///
        homquart_v_blk_p_blk_1wb "\textit{Black victim}" ///
        homquart_v_hsp_p_blk_1wb "\textit{Hispanic victim}" ///
        homquart_v_wht_p_blk_1wb "\textit{White victim}", ///
        nolabel) ///
    cell(mean(fmt(0)))

* 6.3 Exposure within 3/8 mile
foreach r in black hisp white {
    estpost sum homthree8_v_blk_p_blk_1wb homthree8_v_blk_p_hsp_1wb ///
        homthree8_v_blk_p_wht_1wb homthree8_v_blk_p_unk_1wb          ///
        homthree8_v_hsp_p_blk_1wb homthree8_v_hsp_p_hsp_1wb          ///
        homthree8_v_hsp_p_wht_1wb homthree8_v_hsp_p_unk_1wb          ///
        homthree8_v_wht_p_blk_1wb homthree8_v_wht_p_hsp_1wb          ///
        homthree8_v_wht_p_wht_1wb homthree8_v_wht_p_unk_1wb          ///
        if off_race == "`r'"
    estimates store des_`r'
}

esttab des_black des_hisp des_white ///
    using "desc-stats-crime-exposure-by-race-1week-3eights.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("Black officer‑shifts" "Hispanic officer‑shifts" "White officer‑shifts") ///
    refcat( ///
        homthree8_v_blk_p_blk_1wb "\textit{Black victim}" ///
        homthree8_v_hsp_p_blk_1wb "\textit{Hispanic victim}" ///
        homthree8_v_wht_p_blk_1wb "\textit{White victim}", ///
        nolabel) ///
    cell(mean(fmt(0)))	
	
	
	
**************************************************	
**************************************************
* TABLE OF HOMICIDES NEAR OFFICER NEIGHBORHOODS
**************************************************
**************************************************

* 1. Prepare officer buffer IDs
use "masterfile-in-geo-beats-for-analyses.dta", clear
keep uid add_unique_lat_lon off_race
rename add_unique_lat_lon off_nhood_buffer_id
duplicates drop

tempfile officers
save "`officers'", replace

**************************************************
* 2. Join crimes within 1‑mile buffers to officers
**************************************************
use "violent-crimes-linked-to-arrests-2011-2020-happening-within-1-mile-of-unique-officer-2012-2020-addresses.dta", clear

joinby off_nhood_buffer_id using "`officers'", unmatched(master)

**************************************************
* 3. Filter for crimes within 1/4 mile
**************************************************
keep if uid != .
keep if off_crime_dist_miles <= 0.25
keep if crime_crimetype == "HOMICIDE"

**************************************************
* 4. Recode victim's race for LaTeX formatting
**************************************************
replace crime_vict_race = "\\[-1.9ex] \textcolor{white}{1}Black"    if inlist(crime_vict_race, "BLK", "WBH")
replace crime_vict_race = "\\[-1.9ex] \textcolor{white}{3}White"    if crime_vict_race == "WHI"
replace crime_vict_race = "\\[-1.9ex] \textcolor{white}{2}Hispanic" if crime_vict_race == "WWH"
replace crime_vict_race = "\\[-1.9ex] \textcolor{white}{4}Other"    if inlist(crime_vict_race, "API", "I", "UNKNOWN")

**************************************************
* 5. Recode perpetrator's race for LaTeX formatting
**************************************************
gen crime_perp_race = ""
replace crime_perp_race = "\textcolor{white}{1}Black"    if crime_perp_race_blk
replace crime_perp_race = "\textcolor{white}{3}White"    if crime_perp_race_wht
replace crime_perp_race = "\textcolor{white}{2}Hispanic" if crime_perp_race_hsp
replace crime_perp_race = "\textcolor{white}{4}Other"    if crime_perp_race_oth
replace crime_perp_race = "\textcolor{white}{5}No arrest"if crime_perp_race_unk

label var crime_perp_race "\textit{Suspect's race}"
label var crime_vict_race  " "

**************************************************
* 6. Keep unique cases for tabulation
**************************************************
keep unique_crime_id crime_vict_race crime_perp_race off_race
duplicates drop

**************************************************
* 7. Tabulate by officer race groups
**************************************************
foreach r in white black hisp {
    preserve
        keep if off_race == "`r'"
        duplicates drop
        tabout crime_vict_race crime_perp_race ///
            using "desc-stats-crime-`r'-off.tex", replace ///
            style(tex) ///
            cells(freq row) ///
            format(0c 1p) ///
            layout(row) ///
            clab(_ _) ///
            bt ///
            topstr(14cm) ///
            h1( & \multicolumn{6}{c}{\textit{Suspect's race}} \\ \cline{2-7} \\[-1.0ex] ) ///
            h3(\textit{Victim's race} &  &  &  &  & & \\)
    restore
}	




**************************************************
**************************************************
* TABLE OF VICTIM AND PERPETRATOR RACE CITYWIDE
**************************************************
**************************************************

* 1. Load homicide data (exclude 2011)
use violent-crimes-linked-to-arrests-2011-2020.dta, clear
drop if year == 2011
keep if crimetype == "HOMICIDE"

* 2. Recode victim's race for LaTeX formatting
replace vict_race = "\\[-1.0ex] \textcolor{white}{1}Black"    if inlist(vict_race, "BLK", "WBH")
replace vict_race = "\\[-1.0ex] \textcolor{white}{3}White"    if vict_race == "WHI"
replace vict_race = "\\[-1.0ex] \textcolor{white}{2}Hispanic" if vict_race == "WWH"
replace vict_race = "\\[-1.0ex] \textcolor{white}{4}Other"    if inlist(vict_race, "API", "I", "UNKNOWN")

* 3. Recode perpetrator's race for LaTeX formatting
gen perp_race = ""
replace perp_race = "\textcolor{white}{1}Black"    if perp_race_blk
replace perp_race = "\textcolor{white}{3}White"    if perp_race_wht
replace perp_race = "\textcolor{white}{2}Hispanic" if perp_race_hsp
replace perp_race = "\textcolor{white}{4}Other"    if perp_race_oth
replace perp_race = "\textcolor{white}{5}No arrest" if perp_race_unk

* 4. Label variables
lab var perp_race "\textit{Suspect's race}"
lab var vict_race  " "

* 5. Cross-tabulate and export to LaTeX
tabulate vict_race perp_race

tabout vict_race perp_race using "desc-stats-crime-citywide.tex", replace ///
    style(tex) ///
    cells(freq row) ///
    format(0c 1p) ///
    layout(row) ///
    clab(_ _) ///
    bt ///
    topstr(14cm) ///
    h1( & \multicolumn{6}{c}{\textit{Suspect's race}} \\ \cline{2-7} \\[-1.0ex] ) ///
    h3(\textit{Victim's race} &  &  &  &  &   & \\ )
	
	
	
	
**************************************************
**************************************************
* TABLE OF NEIGHBORHOOD DEMOGRAPHICS (EXPOSURES)
**************************************************
**************************************************

* 1. Load masterfile
use "masterfile-in-geo-beats-for-analyses.dta", clear

* 2. Prepare FIPS block‑group identifier
format add_block %19.2g
tostring add_block, gen(blockstr) force
gen FIPS = substr(blockstr, 1, 12)

**************************************************
* COUNT OFFICERS BY RACE IN EACH BLOCK GROUP
**************************************************

gen off_black = (off_race == "black")
gen off_hisp  = (off_race == "hisp")
gen off_white = (off_race == "white")
gen off_aapi  = (off_race == "aapi")

keep uid FIPS off_black off_hisp off_white off_aapi
duplicates drop
sort uid FIPS
by uid: gen order = _n
keep if order == 1

* Sum officers by FIPS
collapse (sum) off_black off_hisp off_white off_aapi, by(FIPS)

**************************************************
* MERGE WITH NEIGHBORHOOD DEMOGRAPHICS
**************************************************

merge m:1 FIPS using "crime-demographics-blockgorup.dta"
drop if _merge == 1
foreach var in off_black off_hisp off_white off_aapi {
    replace `var' = 0 if _merge == 2
}
drop _merge

**************************************************
* COMPUTE EXPOSURE WEIGHTS AND EXPECTED VALUES
**************************************************

foreach g in off chi {
    foreach v in black white hisp {
        egen city_`g'_`v' = sum(`g'_`v')
        gen  wgt_`g'_`v'  = `g'_`v' / city_`g'_`v'
        foreach c in cr_bgr_hom_r cr_bgr_viol_r bg_popdens bg_white bg_black bg_asian bg_hisp ///
                    bg_lesshs bg_college bg_unemployed bg_medhhinc bg_pubassinc bg_poverty bg_veteran bg_housingcost30 {
            gen exp_`g'_`v'_`c' = wgt_`g'_`v' * `c'
        }
    }
}
collapse (sum) exp_*

**************************************************
* RENAME AND RESHAPE FOR TABLE OUTPUT
**************************************************

* Rename by group and race
foreach g in off chi {
    foreach v in black white hisp {
        ren exp_`g'_`v'_* exp_*_`g'_`v'
    }
}

ren *_cr_*    *_*
ren *_bgr_*   *_*
ren *_bg_*    *_*
ren exp_*     *
gen city = 1
ren hom_r* hom*
ren viol_r* viol*

* First reshape: wide by group
reshape long hom_off viol_off popdens_off white_off black_off asian_off hisp_off ///
               lesshs_off college_off unemployed_off medhhinc_off pubassinc_off ///
               poverty_off veteran_off housingcost30_off viol_chi hom_chi popdens_chi ///
               white_chi black_chi asian_chi hisp_chi lesshs_chi college_chi unemployed_chi ///
               medhhinc_chi pubassinc_chi poverty_chi veteran_chi housingcost30_chi, ///
        i(city) j(race) string

* Then reshape: combine group and race dimensions
reshape long viol hom popdens white black asian hisp lesshs college unemployed medhhinc ///
               pubassinc poverty veteran housingcost30, ///
        i(city race) j(group) string

replace group = "officer"   if group == "_off"
replace group = "chicagoan" if group == "_chi"
replace race = substr(race, 2, .)

**************************************************
* LABEL VARIABLES AND SCALE
**************************************************

lab var viol           "\\[-1.0ex] Violent crimes per 100,000"
lab var hom            "\\[-1.0ex] Homicides per 100,000"
lab var popdens        "\\[-1.0ex] Pop. density"
lab var white          "\\[-1.0ex] \% White"
lab var black          "\\[-1.0ex] \% Black"
lab var asian          "\\[-1.0ex] \% Asian"
lab var hisp           "\\[-1.0ex] \% Hispanic"
lab var lesshs         "\\[-1.0ex] \% Less than HS"
lab var college        "\\[-1.0ex] \% College or more"
lab var unemployed     "\\[-1.0ex] \% Unemployed"
lab var medhhinc       "\\[-1.0ex] \% Median household income"
lab var pubassinc      "\\[-1.0ex] \% With public income assistance"
lab var poverty        "\\[-1.0ex] \% Families below poverty line"
lab var veteran        "\\[-1.0ex] \% Veteran"
lab var housingcost30  "\\[-1.0ex] \% Burdened by housing costs"

* Scale popdens and median income vars
replace popdens  = popdens  / 1000
replace medhhinc = medhhinc / 1000

**************************************************
* DESCRIPTIVE STATISTICS BY GROUP & RACE
**************************************************

local demo viol hom popdens white black asian hisp lesshs college unemployed medhhinc pubassinc poverty

foreach g in officer chicagoan {
    foreach r in black white hisp {
        estpost sum `demo' if group == "`g'" & race == "`r'"
        estimates store des_`g'_`r'
    }
}

**************************************************
* EXPORT TO LaTeX
**************************************************

esttab des_officer_black   des_officer_hisp   des_officer_white   ///
       des_chicagoan_black des_chicagoan_hisp des_chicagoan_white ///
    using "desc-stats-neighborhoods.tex", replace ///
    label noobs nonum collabels(none) ///
    mtitles("\shortstack{Black\\officer}" "\shortstack{Hispanic\\officer}" "\shortstack{White\\officer}" ///
            "\shortstack{Black\\Chicagoan}" "\shortstack{Hispanic\\Chicagoan}" "\shortstack{White\\Chicagoan}") ///
    cell(mean(fmt(1)))	
